USE [ssd_banco]
GO

/****** Object:  StoredProcedure [dbo].[calcular_bayes_sp]    Script Date: 06/27/2013 00:57:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE procedure [dbo].[calcular_bayes_sp]
	@plazoFijo int = 1,
	@edad int,
	@ocupacion int,
	@estadoCivil int,
	@niveleducacion int,
	@adeudaCuotas int,
	@tienePrestamosH int,
	@tienePrestamosP int,
	@tipoContacto int,
	@rangoSaldo int,
	@probPlazoFijo decimal(10,4) output,
	@probEdad decimal(10,4) output,
	@probOcupacion decimal(10,4) output,
	@probEstadoCivil decimal(10,4) output,
	@probAdeuda decimal(10,4) output,
	@probPrestH decimal(10,4) output,
	@probPrestP decimal(10,4) output,
	@probTipoContacto decimal(10,4) output,
	@ProbNivelEducacion decimal(10,4) output,
	@ProbRangoSaldo decimal(10,4) output

as
begin
declare @total decimal(10,4) 

set @total = (select cast(count(1) as decimal(10,4)) as prob from dbo.campanaBanco)

set @probPlazoFijo = ((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo)/@total)

if (@probPlazoFijo = cast(0 as decimal(10,4)))
set @probPlazoFijo = (@probPlazoFijo + 1/@total + 2)

set @probEdad =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and edad = @edad)/@total) 

if (@probEdad = cast(0 as decimal(10,4)))
set @probEdad = (@probEdad + 1/@total + 2)

set @probOcupacion =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and ocupacion = @ocupacion)/@total) 

if (@probOcupacion = cast(0 as decimal(10,4)))
set @probOcupacion = (@probOcupacion + 1/@total + 2)

set @probEstadoCivil =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and estadoCivil = @estadoCivil)/@total) 

if (@probEstadoCivil = cast(0 as decimal(10,4)))
set @probEstadoCivil = (@probEstadoCivil + 1/@total + 2)

set @probAdeuda =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and tieneDeuda = @probAdeuda)/@total) 

if (@probAdeuda = cast(0 as decimal(10,4)))
set @probAdeuda = (@probAdeuda + 1/@total + 2)

set @probPrestH =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and tienePrestamoHipotecario = @probPrestH)/@total) 

if (@probPrestH = cast(0 as decimal(10,4)))
set @probPrestH = (@probPrestH + 1/@total + 2)

set @probPrestP =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and tienePrestamoPersonal = @probPrestP)/@total) 

if (@probPrestP = cast(0 as decimal(10,4)))
set @probPrestP = (@probPrestP + 1/@total + 2)

set @probTipoContacto =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and UC_TipoContacto = @probTipoContacto)/@total) 

if (@probTipoContacto = cast(0 as decimal(10,4)))
set @probTipoContacto = (@probTipoContacto + 1/@total + 2)

set @ProbNivelEducacion =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and educacion  = @niveleducacion)/@total) 

if (@ProbNivelEducacion = cast(0 as decimal(10,4)))
set @ProbNivelEducacion = (@ProbNivelEducacion + 1/@total + 2)


set @ProbRangoSaldo =((select cast(COUNT(1) as decimal(10,4)) from dbo.campanaBanco where compraraPlazoFijo = @plazoFijo and saldoCajaAhorro = @rangoSaldo)/@total) 

if (@ProbRangoSaldo = cast(0 as decimal(10,4)))
set @ProbRangoSaldo = (@ProbRangoSaldo + 1/@total + 2)

end






GO


